﻿using BwTMusicModels;
using HelperModels.Album;
using HwjUIDI.Utility.DatabaseHelper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BwTServices.Album
{
    public class AlbumServices
    {
        /// <summary>
        /// 获取所有信息
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<North_Album> GetAllAlbumList(AlbumFilter filter, out int total)
        {
            total = 0;
            List<North_Album> albums = new List<North_Album>();
            total = 0;
            try
            {
                if (filter != null)
                {
                    if (filter.PageSize > 0 && filter.CurrentPage > 0)
                    {
                        StringBuilder sbSQL = new StringBuilder();
                        List<SqlParameter> listParam = new List<SqlParameter>();
                        sbSQL.Append("select top ");
                        sbSQL.Append(filter.PageSize);
                        sbSQL.Append(" * from (select count(1) over() as totalnumber, ROW_NUMBER() over(order by AlbumId) as rownumber, * from North_Album  where '1' = '1'");
                        if (!string.IsNullOrEmpty(filter.AlbumName))
                        {
                            sbSQL.Append(" and AlbumName like '%' + @Name + '%'");
                            SqlParameter param = new SqlParameter("@Name", SqlDbType.NVarChar);
                            param.Value = filter.AlbumName;
                            listParam.Add(param);
                        }
                        sbSQL.Append(") WTX where rownumber > (");
                        sbSQL.Append((filter.CurrentPage - 1) * filter.PageSize);
                        sbSQL.Append(")");
                        using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sbSQL.ToString(), listParam.ToArray()))
                        {
                            while (dr.Read())
                            {
                                int iIndex = 0;
                                North_Album list = new ModelsHelper().GetNorthAlbumModels(dr);
                                albums.Add(list);
                                if (iIndex == 0)
                                {
                                    total = DatabaseFactory.GetDatabase().GetInt(dr, "totalnumber");
                                }
                                iIndex++;
                            }
                        }

                    }
                }
            }
            catch (Exception ex)
            {

            }
            return albums;
        }

        public List<North_Album> GetAllAlbum()
        {
            List<North_Album> albums = new List<North_Album>();
            try
            {
                string sql = "select * from North_Album where IsVaild='N' ";
                using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sql))
                {
                    while (dr.Read())
                    {
                        albums.Add(new ModelsHelper().GetNorthAlbumModels(dr));
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return albums;
        }
        public int ModifyAlbumInfo(North_Album album)
        {
            int bResult = 0;
            try
            {                
                string sql = $"update North_Album set AlbumPic='{album.AlbumPic}',AlbumDesc='{album.AlbumDesc.Replace("'","“”")}',AlbumTime='{album.AlbumTime}',IsVaild='Y' where AlbumId='{album.AlbumId}' ";
                bResult = DatabaseFactory.GetDatabase().ExecNonQuerySQL(sql);
            }
            catch (Exception)
            {
            }        
            return bResult;
        }
        public SingerAlbum GetSingerAlbumInfo(int albumId)
        {
            SingerAlbum singerAlbum = new SingerAlbum();
            try
            {
                string sql = $"select * from SingerAlbum where AlbumId='{albumId}'";
                using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sql))
                {
                    while (dr.Read())
                    {
                        singerAlbum = new ModelsHelper().GetSingerAlbumModels(dr);
                    }
                }
            }
            catch (Exception)
            {
            }           
            return singerAlbum;
        }
    }
}
